const pool = require("../db/conn");
const countControllers = require("../controllers/countControllers");

exports.selectAuthList = async function(teacher_name) {
    let sql =   " SELECT "+
                " 	rc_teacher.teacher_id,  "+
                " 	rc_teacher.teacher_number,  "+
                " 	rc_teacher.teacher_name,  "+
                " 	rc_department.department_id, "+
                " 	rc_department.department_name, "+
                "   concat( rc_teacher.teacher_name,'_',rc_teacher.teacher_number,'_',rc_department.department_name ) as count_text"+
                " FROM "+
                " 	rc_teacher "+
                " 	INNER JOIN "+
                " 	rc_department "+
                " 	ON  "+
                " 		rc_teacher.teacher_department_id = rc_department.department_id "+
                " WHERE "+
                " 	rc_teacher.teacher_is_delete = 0 AND "+
                " 	rc_department.department_is_delete = 0 AND "+
                " 	rc_teacher.teacher_name LIKE ? "
    let conn = await pool.getConnection();
    let [result] = await conn.execute(sql,[ `%${teacher_name}%` ]);
    conn.release();
    return result
}


exports.selectList = async function (pageno,pagesize,tname,department) {    
    let sql =   " SELECT "+
                " 	rc_teacher.teacher_id,  "+
                " 	rc_teacher.teacher_number,  "+
                " 	rc_teacher.teacher_name,  "+
                " 	rc_department.department_id, "+
                " 	rc_department.department_name "+
                " FROM "+
                " 	rc_teacher "+
                " 	LEFT JOIN "+
                " 	rc_department "+
                " 	ON  "+
                " 		rc_teacher.teacher_department_id = rc_department.department_id "+
                " WHERE "+
                " 	rc_teacher.teacher_is_delete = 0  ";
    let params = [];
    if(tname!=null&&tname!=""){
        sql += "AND rc_teacher.teacher_name like ? "
        params.push( `%${tname}%` );
    }
    if(department!=null&&department!=""){
        sql += "AND rc_department.department_name like ? "
        params.push( `%${department}%` );
    }

    let data = await countControllers.selectCount( sql,params );
    
    sql += "LIMIT ?, ?";
    let start = ((pageno-1)*pagesize)+"";
    let size = pagesize;
    
    params.push(start,size);

    let conn = await pool.getConnection();
    let [result] = await conn.execute(sql,params);
    conn.release();

    data.pageNo = pageno*1
    data.pageSize = pagesize*1;
    data.list = result;
    return data;
}

exports.selectById = async function (teacher_id) {
    let sql =   "SELECT"+
                " 	rc_teacher.teacher_id,  "+
                " 	rc_teacher.teacher_department_id,  "+
                " 	rc_teacher.teacher_number,  "+
                " 	rc_teacher.teacher_name,  "+
                " 	rc_teacher.teacher_is_delete,  "+
                " 	rc_teacher.teacher_role_id  "+
                " FROM "+
                " 	rc_teacher "+
                " WHERE "+
                "	rc_teacher.teacher_id = ? ";

    let conn = await pool.getConnection();
    let [result] = await conn.execute(sql,[teacher_id]);
    conn.release();
    
    return result[0];
}

exports.selectByNameAndCondition = async function (teacher_number,teacher_id,teacher_is_delete) {
    let sql =   "SELECT"+
                " 	rc_teacher.teacher_id,  "+
                " 	rc_teacher.teacher_department_id,  "+
                " 	rc_teacher.teacher_number,  "+
                " 	rc_teacher.teacher_name,  "+
                " 	rc_teacher.teacher_is_delete,  "+
                " 	rc_teacher.teacher_role_id  "+
                " FROM "+
                " 	rc_teacher "+
                "WHERE "+
                "	rc_teacher.teacher_number = ? ";

    let params = [teacher_number];
    if(teacher_id!=null&&teacher_id!=""){
        sql += "AND rc_teacher.teacher_id != ? ";
        params.push( teacher_id );
    }
    if(teacher_is_delete!=null&&teacher_is_delete!=""){
        sql += "AND rc_teacher.teacher_is_delete = ? ";
        params.push( teacher_is_delete );
    }

    let conn = await pool.getConnection();
    let [result] = await conn.execute(sql,params);
    conn.release();
    
    return result[0];
}

exports.insert = async function (params,state=0) {
    let sql =  "insert into rc_teacher values (null,?,?,?,'123456',?,3)";

    let conn = await pool.getConnection();
    let [{changedRows}] = await conn.execute(sql,[params.teacher_department_id,
                                                  params.teacher_number,
                                                  params.teacher_name,
                                                  state
                                                ]);
    conn.release();
    
    return changedRows!=0;
}

exports.updateByDid = async function (teacher_department_id,teacher_number,teacher_name,teacher_id,state=0) {
    let sql =   " UPDATE "+
                " 	rc_teacher"+
                " SET "+
                " 	rc_teacher.teacher_department_id = ?, "+
                " 	rc_teacher.teacher_number = ?, "+
                " 	rc_teacher.teacher_name = ?, "+
                " 	rc_teacher.teacher_is_delete = ? "+
                " WHERE "+
                " 	rc_teacher.teacher_id = ? ";

    let conn = await pool.getConnection();
    let [{changedRows}] = await conn.execute(sql,[teacher_department_id,teacher_number,teacher_name,state,teacher_id]);
    conn.release();
    
    return changedRows!=0;
}

exports.deleteByDid = async function (teacher_id) {
    let sql =  "UPDATE rc_teacher SET teacher_is_delete = 1 WHERE teacher_id = ?";

    let conn = await pool.getConnection();
    let [{changedRows}] = await conn.execute(sql,[teacher_id]);
    conn.release();
    
    return changedRows!=0;
}
